package xdpm.nhom16.DAO;

import java.util.ArrayList;

import xdpm.nhom16.DTO.DuLieuDTO;
import xdpm.nhom16.DTO.DuLieuSearchDTO;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class DuLieuDAO extends AbstractDAO<DuLieuDTO> {

	public static final String COLUMN_ID = "MaDuLieu";
	public static final String[] NAME_COLUMNS = new String[] { COLUMN_ID, "MaDichVu", "MaTenDiaDiem", "SoNha",
			"MaDuong", "MaPhuong", "MaQuanHuyen", "MaTinhThanh", "KinhDo",
			"ViDo", "ChuThich" };
	public static final String TABLE_NAME = "DULIEU";

	public DuLieuDAO(SQLiteDatabase db) {
		super(db);
	}

	@Override
	protected String getTableName() {
		// TODO Auto-generated method stub
		return TABLE_NAME;
	}

	@Override
	protected String getNameColumnId() {
		// TODO Auto-generated method stub
		return COLUMN_ID;
	}

	@Override
	protected String[] getNameColumns() {
		// TODO Auto-generated method stub
		return NAME_COLUMNS;
	}

	@Override
	protected DuLieuDTO cusorToDTO(Cursor cursor) {
		// TODO Auto-generated method stub
		DuLieuDTO result = new DuLieuDTO();
		result.setMaDuLieu(cursor.getInt(0));
		if (!cursor.isNull(1))
			result.setMaDichVu(cursor.getInt(1));
		if (!cursor.isNull(2))
			result.setMaTenDiaDiem(cursor.getInt(2));
		if (!cursor.isNull(3))
			result.setSoNha(cursor.getString(3));
		if (!cursor.isNull(4))
			result.setMaDuong(cursor.getInt(4));
		if (!cursor.isNull(5))
			result.setMaPhuong(cursor.getInt(5));
		if (!cursor.isNull(6))
			result.setMaQuanHuyen(cursor.getInt(6));
		if (!cursor.isNull(7))
			result.setMaTinhThanh(cursor.getInt(7));
		if (!cursor.isNull(8))
			result.setKinhDo(cursor.getDouble(8));
		if (!cursor.isNull(9))
			result.setViDo(cursor.getDouble(9));
		if (!cursor.isNull(10))
			result.setChuThich(cursor.getString(10));
		return result;
	}

	public String[] searchSoNha(String input) {
		ArrayList<String> result = new ArrayList<String>();
		String query = String.format(
				"SELECT DISTINCT SoNha FROM DuLieu WHERE LENGTH( REPLACE('%s',SoNha,''))"
						+ "!= '%s'", input, input);
		Cursor cursor = database.rawQuery(query, null);
		if (null == cursor || !cursor.moveToFirst())
			return null;
		while (!cursor.isAfterLast()) {
			if (!cursor.isNull(0)) {
				String soNha = cursor.getString(0);
				if (soNha.trim().length() != 0 && input.indexOf(soNha) != -1) {
					result.add(soNha);
				}
			}
			cursor.moveToNext();
		}

		return result.toArray(new String[result.size()]);
	}

	public ArrayList<DuLieuDTO> SearchWithSoNha(String input) {
		ArrayList<DuLieuDTO> result = new ArrayList<DuLieuDTO>();
		Cursor cursor = database.query(getTableName(), getNameColumns(), null,
				null, null, null, null);
		if (null == cursor || !cursor.moveToFirst())
			return result;
		while (!cursor.isAfterLast()) {
			if (cursor.isNull(3)) {
				String soNha = cursor.getString(3);
				if (input.indexOf(soNha) != -1) {
					DuLieuDTO dulieu = this.cusorToDTO(cursor);
					result.add(dulieu);
				}
			}
			cursor.moveToNext();
		}
		cursor.close();
		return result;
	}

	public ArrayList<DuLieuDTO> Search(String soNha, int maDichVu,
			int maTinhThanh, int maQuanHuyen, int maPhuong, int maDuong,
			int maTenDiaDiem) {

		String selection = "";
		if (soNha != null && soNha.trim().length() > 0) {
			selection += String.format(" %s='%s' AND ", "SoNha", soNha);
		}
		if (maDichVu != DEFAULT_NUMBER) {
			selection += String.format(" %s='%d' AND ", "MaDichVu", maDichVu);
		}
		if (maTinhThanh != DEFAULT_NUMBER) {
			selection += String.format(" %s='%d' AND ", "MaTinhThanh",
					maTinhThanh);
		}
		if (maQuanHuyen != DEFAULT_NUMBER) {
			selection += String.format(" %s='%d' AND ", "MaQuanHuyen",
					maQuanHuyen);
		}
		if (maPhuong != DEFAULT_NUMBER) {
			selection += String.format(" %s='%d' AND ", "MaPhuong", maPhuong);
		}
		if (maDuong != DEFAULT_NUMBER) {
			selection += String.format(" %s='%d' AND ", "MaDuong", maDuong);
		}
		if (maTenDiaDiem != DEFAULT_NUMBER) {
			selection += String.format(" %s='%d' AND ", "MaTenDiaDiem",
					maTenDiaDiem);
		}
		int lastOfAnd = selection.lastIndexOf("AND");
		if (lastOfAnd != -1) {
			selection = selection.substring(0, lastOfAnd);
		}
		Cursor cursor = database.query(getTableName(), getNameColumns(),
				selection, null, null, null, null);
		ArrayList<DuLieuDTO> result = cursorToDTOs(cursor);
		return result;
	}
	public ArrayList<DuLieuSearchDTO> searchForSort(int iMaDichVu, int iMaDuong)	{
		ArrayList<DuLieuSearchDTO> lstSearch = new ArrayList<DuLieuSearchDTO>();
		String selection = "";
		if (iMaDichVu > 0)	{
			selection += " AND DULIEU.MaDichVu=" + iMaDichVu;
		}
		if (iMaDuong > 0)	{
			selection += " AND DULIEU.MaDuong=" + iMaDuong;
		}
		String query = "SELECT DULIEU.MaDuLieu, DICHVU.MaDichVu, DICHVU.TenDichVu,"
				+ " TENDIADIEM.TenDiaDiem, DULIEU.SoNha, DUONG.TenDuong, PHUONG.TenPhuong, QUANHUYEN.TenQuanHuyen"
				+ " FROM DULIEU, TENDIADIEM, DICHVU, DUONG, PHUONG, QUANHUYEN"
				+ " WHERE"
				+ " DULIEU.MaDichVu = DICHVU.MaDichVu"
				+ " AND DULIEU.MaTenDiaDiem = TENDIADIEM.MaTenDiaDiem"
				+ " AND DULIEU.MaDuong = DUONG.MaDuong"
				+ " AND DULIEU.MaPhuong = PHUONG.MaPhuong"
				+ " AND DULIEU.MaQuanHuyen = QUANHUYEN.MaQuanHuyen"
				+ selection;
		/*
		String query = "SELECT DULIEU.MaDuLieu, DICHVU.MaDichVu, DICHVU.TenDichVu,"
				+ " TENDIADIEM.TenDiaDiem, DULIEU.SoNha, DUONG.TenDuong, PHUONG.TenPhuong, QUANHUYEN.TenQuanHuyen,"
				+ " MAX(CHITIETDICHVU.GiaTien), MIN(CHITIETDICHVU.GiaTien)"
				+ " FROM DULIEU, TENDIADIEM, DICHVU, DUONG, PHUONG, QUANHUYEN, CHITIET_DULIEU, CHITIETDICHVU"
				+ " WHERE"
				+ " DULIEU.MaDichVu = DICHVU.MaDichVu"
				+ " AND DULIEU.MaTenDiaDiem = TENDIADIEM.MaTenDiaDiem"
				+ " AND DULIEU.MaDuong = DUONG.MaDuong"
				+ " AND DULIEU.MaPhuong = PHUONG.MaPhuong"
				+ " AND DULIEU.MaQuanHuyen = QUANHUYEN.MaQuanHuyen"
				+ " AND DULIEU.MaDuLieu = CHITIET_DULIEU.MaChiTiet"
				+ " AND CHITIET_DULIEU.MaChiTiet = CHITIETDICHVU.MaChiTiet"
				+ selection
				+ " GROUP BY CHITIET_DULIEU.MaDuLieu";*/
		Log.e("GETDTO", "query: " + query);
		Cursor cursor = database.rawQuery(query, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast())
		{
			DuLieuSearchDTO dto = cursorToDuLieuSearch(cursor);
			lstSearch.add(dto);
			cursor.moveToNext();
		}
		return lstSearch;
	}
	public DuLieuSearchDTO cursorToDuLieuSearch(Cursor cursor)	{
		DuLieuSearchDTO duLieuSearch = new DuLieuSearchDTO();
		if (!cursor.isNull(0)) {
			duLieuSearch.setMaDuLieu(cursor.getInt(0));
		}
		if (!cursor.isNull(1)) {
			duLieuSearch.setMaDichVu(cursor.getInt(1));
		}
		if (!cursor.isNull(2)) {
			duLieuSearch.setTenDichVu(cursor.getString(2));
		}
		if (!cursor.isNull(3)) {
			duLieuSearch.setTenDiaDiem(cursor.getString(3));
		}
		String tmpDiaChi = "";
		if (!cursor.isNull(4)) {
			tmpDiaChi += cursor.getString(4);
		}
		if (!cursor.isNull(5)) {
			tmpDiaChi += " " + cursor.getString(5);
		}
		if (!cursor.isNull(6)) {
			tmpDiaChi += ", Phường " + cursor.getString(6);
		}
		if (!cursor.isNull(7)) {
			tmpDiaChi += ", Quận " + cursor.getString(7);
		}
		duLieuSearch.setGiaCaoNhat(0);
		duLieuSearch.setGiaThapNhat(0);
		
		duLieuSearch.setDiaChi(tmpDiaChi);
		return duLieuSearch;
	}
}
